1. Field of the Invention
This invention relates to a query generator and to a method for generating queries, in particular for use with relational databases.
2. Description of Related Art
Query generators, for example Discoverer® from Oracle®, are used to construct database queries which are then sent to a database for execution. The user constructs a query by selecting items from a drop-down list of items displayed on the screen. The items represent data which are to be obtained from the database. In addition, the user may select one or more operations that are to be performed on the data. Once the items and operations have been selected, the query generator automatically generates a query, usually in structured query language (SQL), for execution by the database.
It is common with complex databases to define a view that exposes only some parts of a database which the majority of users typically require to access. Such a view is useful since it abstracts some of the complexity of the underlying data structure from the users whilst allowing them to see the data that they generally require to see. However, whilst a particular view may suffice for most of the users of a database there may be some users who require to see not only the data exposed by the view but also data from the database that are not exposed by the view.
In a query tool, a user can select the items to be retrieved from one or more tables or views of the database. In Oracle® Discoverer® these are known as folders. Once an item has been selected from one table or view (or folder), it is possible to select an item from another folder by way of a join, which may be created by the database administrator. In the case of Oracle® Discoverer®, a metadata item is held indicating that a join has been defined between two folders, and also which items are required to be retrieved from the database. Such a join may be created between entirely unrelated tables or views and may be an inner join or an outer join.
For example, FIG. 2 shows a table T in a database. A view V has been defined which exposes a column C1 in the table T (and may expose other items from the database including those from other tables). If a user requires to retrieve the data items from column C1 of view V and also the corresponding data items from column 2 of table T they would select these items from a list in a tool such as Oracle® Discoverer® which would then generate the SQL statement shown below:
SELECT T.C2, V.C1
FROM T, V
WHERE T.C1=V.C1
As can be seen, this SQL statement refers to items from table T and from view V, and corresponding items are paired by virtue of the join condition T.C1=V.C1. In actual fact, this statement will be reduced to that shown below in which the reference to V is replaced by its SQL definition as an inline view:
SELECT T.C2, V.C1
FROM T, (SELECT C1 FROM T) V
WHERE T.C1=V.C1
As can be seen, this SQL statement refers to table T twice (by reference to columns T.C1 and T.C2). In addition, by virtue of the definition of the inline view V (i.e. that it is based entirely on table T), the SQL statement also causes execution of a join from table T back to itself. This is obviously inefficient, and in a complex database with a large number of tables, creating redundant joins for all these tables can waste a large amount of processing time.